-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    
    -- 基本信息
    full_name VARCHAR(100),
    avatar_url VARCHAR(500),
    bio TEXT,
    location VARCHAR(100),
    website VARCHAR(200),
    
    -- 技能和标签
    skills JSONB DEFAULT '[]'::jsonb,
    interests JSONB DEFAULT '[]'::jsonb,
    
    -- 状态和权限
    is_active BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    is_admin BOOLEAN DEFAULT false,
    
    -- 统计数据
    competitions_joined INTEGER DEFAULT 0,
    projects_created INTEGER DEFAULT 0,
    teams_joined INTEGER DEFAULT 0,
    reputation_score INTEGER DEFAULT 0,
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
CREATE INDEX IF NOT EXISTS idx_users_reputation_score ON users(reputation_score);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为用户表创建更新时间触发器
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 启用 RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- 创建策略
DROP POLICY IF EXISTS "Users can view public profiles" ON users;
CREATE POLICY "Users can view public profiles" ON users
    FOR SELECT USING (true);

DROP POLICY IF EXISTS "Users can update own profile" ON users;
CREATE POLICY "Users can update own profile" ON users
    FOR UPDATE USING (auth.uid()::text = id::text);

DROP POLICY IF EXISTS "Users can insert own profile" ON users;
CREATE POLICY "Users can insert own profile" ON users
    FOR INSERT WITH CHECK (auth.uid()::text = id::text);

-- 授权
GRANT SELECT ON users TO anon;
GRANT ALL PRIVILEGES ON users TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO authenticated;

-- 插入测试用户数据
INSERT INTO users (email, username, hashed_password, full_name, bio, skills, interests, is_verified) VALUES
('alice@example.com', 'alice_dev', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/VcQs7dp1K', 'Alice Johnson', '全栈开发者，专注于React和Python', '["React", "Python", "JavaScript", "Node.js"]', '["Web Development", "AI", "Open Source"]', true),
('bob@example.com', 'bob_ml', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/VcQs7dp1K', 'Bob Smith', '机器学习工程师，热爱数据科学', '["Python", "TensorFlow", "PyTorch", "SQL"]', '["Machine Learning", "Data Science", "AI"]', true),
('charlie@example.com', 'charlie_design', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/VcQs7dp1K', 'Charlie Brown', 'UI/UX设计师，关注用户体验', '["Figma", "Sketch", "Adobe XD", "Prototyping"]', '["UI/UX Design", "User Research", "Design Systems"]', true),
('diana@example.com', 'diana_data', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/VcQs7dp1K', 'Diana Lee', '数据分析师，擅长数据可视化', '["Python", "R", "Tableau", "Power BI"]', '["Data Analysis", "Statistics", "Business Intelligence"]', true),
('eve@example.com', 'eve_security', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/VcQs7dp1K', 'Eve Wilson', '网络安全专家，专注于渗透测试', '["Penetration Testing", "Cybersecurity", "Linux", "Python"]', '["Cybersecurity", "Ethical Hacking", "Network Security"]', true);